<?php
include("constants.php");

class MySQLDB{
    var $connection;
    
    //Class Constructor
    function MySQLDB(){
        $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
        mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
    }
    
    //gets item information from database
    function getItemsInfo(){
        $sql = "SELECT items.itemsID, items.categoryID, items.name,
                (SELECT GROUP_CONCAT(foodCodeID) 
                 FROM foodcodeitemrelation
                 WHERE itemsID = items.itemsID) AS foodCodes
                FROM items ORDER BY categoryID";
        
        $result = mysql_query($sql, $this->connection);
        /* Error occurred, return given name by default */
        if(!$result || (mysql_numrows($result) < 1)){
            return NULL;
        }
        /* Return result array */
        $i = 0;
        while($row = mysql_fetch_array($result)){
            $itemsArray[$i]['itemsID'] = $row['itemsID'];
            $itemsArray[$i]['categoryID'] = $row['categoryID'];
            $itemsArray[$i]['name'] = $row['name'];
            $itemsArray[$i]['foodCodes'] = $row['foodCodes'];
            $i++;
        }
        
        return $itemsArray;
    }
	
     /*
     * Returns the sum of purchased items by Food Code.
     * Format of [name][description][total] 
     * */
    function getLogCount($start, $end)
    {
        $query = "SELECT foodcodes.name, foodcodes.description, COUNT(*) as total
                    FROM logs 
                    JOIN foodcodes
                    ON logs.foodcodeID = foodcodes.foodcodeID
                    WHERE logs.orderDate >= '".$start."'
                    AND logs.orderDate <= '".$end."'
                    GROUP BY foodcodes.name";
            
        $result = mysql_query($query, $this->connection);
        /* Error occurred, return given name by default */
        if(!$result || (mysql_numrows($result) < 1)){
            return NULL;
        }
            
        return $result;
            
    }
    
     /* 
     * Returns the next days special(s)
     * Format of [name]
     * */
    function getSpecial($start,$end){
        $query = "SELECT foodCodeID
                    FROM logs
                    WHERE logs.orderDate >= '".$start."'
                    AND logs.orderDate <= '".$end."'
                    GROUP BY foodCodeID
                    ORDER BY count(foodCodeID) Desc
                    LIMIT 1";

        $result = mysql_query($query, $this->connection);
        /* Error occurred, return given name by default */
        if(!$result || (mysql_numrows($result) < 1)){
            return NULL;
        }

        while($row = mysql_fetch_array($result)) {
          $foodCodeID =  $row['foodCodeID'];
        }

        //use results from above query to get the itemID foods
        //that appeard the least in the category with the most ordered foos
        $query = "SELECT itemsID
                    FROM logs
                    WHERE logs.orderDate >= '".$start."'
                    AND logs.orderDate <= '".$end."'
                    AND foodCodeID = '".$foodCodeID."' ";

        $result = mysql_query($query, $this->connection);
        /* Error occurred, return given name by default */
        if(!$result || (mysql_numrows($result) < 1)){
            return NULL;
        }

        $foodItemResult = array(); //array to hold the itemsID's
        $count=0;

        //this loop will populate foodItemResult array with itemsID's
        while($row = mysql_fetch_array($result)) {
            $foodCodeID =  $row['itemsID'];
            $foodItemResult[$count] = $foodCodeID;
            $count++;
        }

        $leastOrderedItemsID = array(); //this array holds the itemID's of theleast ordered items
        $answer = $this->ExtractLeasts($foodItemResult); //populate the array with the least ordered items

        //create a string to use as input for the sql query below to get the food name(s)
        //of the least ordered items
        $leftBracket = '(';
        $rightBracket = ')';
        $string1 = "";
        for($j=0; $j < count($answer); $j++){
            $string1 .= $answer[$j];
            $string1 .= ',';
        }

        $string2 = substr($string1,0,-1);
        $string3 = $leftBracket.$string2.$rightBracket;

        //sql query to return the names of the least ordered items
        $query = "select name
                  from items
                  where itemsID in $string3";

        $result = mysql_query($query, $this->connection);
        /* Error occurred, return given name by default */
        if(!$result || (mysql_numrows($result) < 1)){
            return NULL;
        }

        return $result;
    }
    
    //this function returns an array of itemsID of the least ordered foods
    //from the foodItemResult array
    function ExtractLeasts($arr){
        if(!$arr){
            return NULL;
        }
        else{
            $leasts = array();
            sort($arr, SORT_NUMERIC);
            $arrCount = array_count_values($arr);
            asort($arrCount);

            foreach ($arrCount as $k=>$v){
                $least = $v;
                break;
            }
            foreach ($arrCount as $k=>$v){
                if ($v == $least){
                    $leasts[] = $k;
                }
            }
            return $leasts;
        }
    }// end function ExtractLeast
    
    function createLogFromOrder($itemID, $foodCodes, $theDate){
        $foodCodeIDs = explode(',', $foodCodes);
        
        foreach($foodCodeIDs as $code){
            //echo 'itemID='.$itemID.' foodCode='.$code.' Date='.$theDate.'<br />';
            $sql = "INSERT INTO logs(itemsID, foodCodeID, orderDate) VALUES('".$itemID."', '".$code."', '".$theDate."')";            
            mysql_query($sql, $this->connection);
        }
    }
};

//Create DB connection
$database = new MySQLDB;
?>
